テクサポと学ぶ PostgreSQL #4 createuser

#PostgreSQL

#RDS for PostgreSQL

村上紗敏

2024.07.07

アノテーション、Postgre 大好き村上です。

シリーズ第 3 回目テクサポと学ぶ PostgreSQL #3 ユーザー(2) では、CREATE USER 文と CREATE ROLE 文で作成することができるロールの属性の違いに注目してみました。シリーズ第 4 回目は、クライアントユーティリティ createuser を利用してのリモート接続とオプション指定での属性の付与について試してみます。

本シリーズでしていくこと

PostgreSQL に対しての基本的な知識を確認しながら、時には EC2 上にインストールした PostgreSQL 15 において DDL や DML 等のコマンドを実行して動作確認をしていきます。また、業務で RDS for PostgreSQL や Aurora PostgreSQL へのお問い合わせに対応しているので、設定や運用面でお役に立つような情報も合わせてご紹介していきます。

createuser ユーティリティとは？

createuser

createuserは新しいPostgreSQLのユーザ（より正確にいえばロール）を作成します。新しいユーザを作成できるのは、スーパーユーザとCREATEROLE権限を持つユーザのみです。したがって、createuserは、スーパーユーザもしくはCREATEROLE権限を持つユーザとして接続可能なユーザによって実行されなければなりません。

psql ツールと同様に、UNIX ドメインでのローカル接続とクライアントからのリモート接続が可能です。オプションを指定してロールを作成する前に、createuser ユーティリティを利用してのリモート接続を試してみます。以下の表は、リモート接続時によく利用するオプションの説明となります。

オプション（省略形）	オプション	説明
-h host	--host=host	サーバが稼働しているマシンのホスト名を指定します。この値がスラッシュから始まる場合、Unixドメインソケット用のディレクトリとして使用されます。
-p port	--port=port	サーバが接続を監視するTCPポートもしくはUnixドメインソケットファイルの拡張子を指定します。
-U username	--username=username	接続に使用するユーザ名です（作成するユーザの名前ではありません）。

リモート接続してみた

ハマりポイント

リモート接続時は、PostgreSQL をインストールしているサーバーのどのネットワークインターフェースを経由して PostgreSQL への TCP/IP 接続を許可するのか、postgresql.conf ファイル上の listen_addresses において指定する必要があります。 EC2 上で PostgreSQL を動かす場合は、どうしてもセキュリティグループやネットワーク ACL の設定に不備が無いかを重点的に確認すると思うので、listen_addresses の設定を忘れないようにしましょう。

postgresql.conf ファイルのデフォルト設定

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)

以下のように postgresql.conf ファイルを編集した後に再起動することで、EC2 上のどのネットワークインターフェースを経由した接続についても PostgreSQL へのリモート接続が可能になります。

postgresql.conf ファイル（修正済み）

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'                  # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)

なお、追加の設定として接続元 EC2 のプライベート IP アドレスからのリモート接続を pg_hba.conf ファイルにおいて許可する必要があります。以下のように pg_hba.conf ファイルを編集した後にリロードして設定を反映させます。

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     scram-sha-256
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
>host    all             all             10.0.30.29/32           scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

PostgreSQL がインストールされている EC2 とは異なる EC2 からデフォルトユーザー postgres を利用して、リモート接続でオプションを指定せずにユーザー remoteuser を作成してみます。

[ec2-user@ip-10-0-30-29 ~]$ createuser -U postgres -h 10.0.30.28 -p 5432 remoteuser
Password:
[ec2-user@ip-10-0-30-29 ~]$

PostgreSQL がインストールされている EC2 において、ユーザー remoteuser が作成されているかをメタコマンド \du で確認してみます。

postgres=# \du
                                    List of roles
 Role name  |                         Attributes                         | Member of
------------+------------------------------------------------------------+-----------
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 remoteuser |                                                            | {}

デフォルトユーザー postgres とは別に remoteuser が作成されていることが確認できました！以降は、ローカル接続で createuser ユーティリティを利用して、オプションでの属性の付与を試してみます。

オプションを指定してロールを作成してみた

createuser を利用してロールを作成した場合も、特別な属性をロールに付与できるようなことはありません。基本的には、CREATE ROLE 文を実行してロールを作成するのと変わりませんが、--interactive オプションで対話的にロールを作成することも可能です。以下、よく使うオプションを一部抜粋します。
createuser

createuser は SQL コマンド CREATE ROLE のラッパーです。このユーティリティによってユーザを作成しても、これ以外の方法でサーバにアクセスしてユーザを作成しても特に違いはありません。

オプション（省略形）	オプション	説明
-d	--createdb	新しいユーザに対してデータベースの作成を許可します。
-e	--echo	createuserが生成しサーバに送信するコマンドを出力します。
-g role	--role=role	このロールが新しいメンバーとして即座に追加されるロールを示します。複数の-gスイッチを記述することで、このロールがメンバーとして追加される複数のロールを指定することができます。
-i	--inherit	新しいロールは自動的にメンバとして属するロールの権限を継承します。これがデフォルトです。
	--interactive	ユーザ名がコマンドラインで指定されない場合、ユーザ名の入力を促し、更に -d/-D、-r/-R、-s/-Sオプションがコマンドラインで指定されない場合にはどちらにするか入力を促します。
-l	--login	新しいユーザに対してログインを許可します。（つまり、このユーザ名をセッション起動時のユーザ識別子として使用することができます。）これがデフォルトです。
-P	--pwprompt	このオプションが指定されると、createuserは新しいユーザのパスワードのプロンプトを表示します。もしパスワード認証を使う予定がなければ、これは必要ありません。
-r	--createrole	他のロールの作成、変更、削除、コメント付与、セキュリティラベルの変更、およびメンバ資格の付与または取消しを、新しいユーザに対して許可します。つまり、このユーザはCREATEROLE権限を持つことになります。この権限によって付与される機能の詳細については、ロールの作成を参照してください。
-s	--superuser	新しいユーザはスーパーユーザになります。
	--replication	新しいユーザはREPLICATION権限を持ちます。
-?	--help	createuserのコマンドライン引数の使用方法を表示し、終了します。

試しにデフォルトユーザー postgres 以外のスーパーユーザーを createuser で作成してみます。オプションで新規ロールのパスワードを指定します。実際に実行される SQL 文についても確認できるようにオプション -e（--echo）も指定してみます。

[postgres@ip-10-0-30-28 ~]$ createuser adminuser -P -s -e
Enter password for new role:
Enter it again:
Password:
SELECT pg_catalog.set_config('search_path', '', false);
CREATE ROLE adminuser PASSWORD 'SCRAM-SHA-256$4096:EU7mTWqLlDvqin7YmT8mZw==$fuz83o73IKgslp+5AQccnuotw+w9RKfYHXhBJlHWzB4=:YjnKTqlNh20MNFSkmk2i2E5xpg443TtbEzVsZcF1gxc=' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;

実際に PostgreSQL へ送信されている CREATE ROLE 文を確認すると、パスワードとスーパーユーザー権限を持つロールであることが確認できます。ちなみにスーパーユーザーについても、ALTER ROLE 文でログイン属性を外すと PostgreSQL にログインができなくなります。デフォルトユーザー postgres 以外のスーパーユーザーを作成した後は、セキュリティ面を考慮してデフォルトユーザー postgres のログイン属性を外すこともご検討ください。
4 行目の Password: は、デフォルトユーザー postgres の PostgreSQL ログイン時のパスワードを入力します。（オプション -U で CREATEROLE 属性を持つ他ユーザーで操作する場合はそのユーザーのパスワードを入力します。）
なお、参考までにオプション --interactive を指定して対話的にロールを作成する場合は、以下のようになります。

[postgres@ip-10-0-30-28 ~]$ createuser sampleuser --interactive -e
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
Password:
SELECT pg_catalog.set_config('search_path', '', false);
CREATE ROLE sampleuser NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;